﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DomainModel;  // must add this...
using System.Configuration; // must add this...
using System.Data.SqlClient; // must add this...
using System.Data; // must add this...

namespace DAL
{
  public static class DALTv
  {
    static string connection_string = ConfigurationSettings.AppSettings["dsn"];

    //public static void BlockCustomer()
    //public static void UnblockCustomer()
    //public static void ChangePermissions()
    /*******CUSTOMER PROCS**************/
      
    //this procedure inserts a customer
    public static void InsertCustomer(Customer customer, ref List<string> errors)
    {
      SqlConnection conn = new SqlConnection(connection_string);
      try
      {
        string strSQL = "insert_customer";

        SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
        mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
       // mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.VarChar, 50));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 50));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@first_name", SqlDbType.VarChar, 50));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@last_name", SqlDbType.VarChar, 50));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@address", SqlDbType.VarChar, 50));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@state", SqlDbType.VarChar, 50));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@zip", SqlDbType.Int));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@city", SqlDbType.VarChar, 50));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@webmaster", SqlDbType.Int));
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@blockuser", SqlDbType.Int));

      //  mySA.SelectCommand.Parameters["@customer_id"].Value = customer.customer_id;
        mySA.SelectCommand.Parameters["@email"].Value = customer.email;
        mySA.SelectCommand.Parameters["@first_name"].Value = customer.first_name;
        mySA.SelectCommand.Parameters["@last_name"].Value = customer.last_name;
        mySA.SelectCommand.Parameters["@address"].Value = customer.address;
        mySA.SelectCommand.Parameters["@state"].Value = customer.state;
        mySA.SelectCommand.Parameters["@zip"].Value = customer.zip;
        mySA.SelectCommand.Parameters["@city"].Value = customer.city;
        mySA.SelectCommand.Parameters["@webmaster"].Value = customer.webmaster;
        mySA.SelectCommand.Parameters["@blockuser"].Value = customer.blockuser;

        DataSet myDS = new DataSet();
        mySA.Fill(myDS);

      }
      catch (Exception e)
      {
        errors.Add("Error: " + e.ToString());
      }
      finally
      {
        conn.Dispose();
        conn = null;
      }
    }

    //This procedure deletes a customer
    public static void DeleteCustomer(int id, ref List<string> errors)
    {
      SqlConnection conn = new SqlConnection(connection_string);

      try
      {
        string strSQL = "delete_customer";

        SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
        mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
        mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.VarChar, 50));

        mySA.SelectCommand.Parameters["@customer_id"].Value = id;

        DataSet myDS = new DataSet();
        mySA.Fill(myDS);

      }
      catch (Exception e)
      {
        errors.Add("Error: " + e.ToString());
      }
      finally
      {
        conn.Dispose();
        conn = null;
      }
    }

    //public static void DeleteAllRecords()

    //this procedure gets an email
    public static string GetEmail(int id, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        string email = "";

        try
        {
            string strSQL = "get_customer_email";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.VarChar, 50));
            
            mySA.SelectCommand.Parameters["@email"].Value = id;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            email = myDS.Tables[0].Rows[0]["email"].ToString();
        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return email;
    }

    //public static int GetCustomerId()

    //This procedure gets customer names
    public static string GetName(int id, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        string full_name = "";
        try
        {
            string strSQL = "get_customer_names";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            //mySA.SelectCommand.Parameters.Add(new SqlParameter("@first_name", SqlDbType.VarChar, 50));
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.VarChar, 50));
            
            mySA.SelectCommand.Parameters["@customer_id"].Value = id;
            //mySA.SelectCommand.Parameters["@last_name"].Value = customer.last_name;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            full_name = myDS.Tables[0].Rows[0]["first_name"].ToString() + " " +
                 myDS.Tables[0].Rows[0]["last_name"].ToString();

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return full_name;
    }

    //gets the residence of the customer
    public static string GetResidence(int id, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        string residence = "";

        try
        {
            string strSQL = "get_customer_residence";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.VarChar, 50));
            //mySA.SelectCommand.Parameters.Add(new SqlParameter("@state", SqlDbType.VarChar, 50));
            //mySA.SelectCommand.Parameters.Add(new SqlParameter("@zip", SqlDbType.Int));
            //mySA.SelectCommand.Parameters.Add(new SqlParameter("@city", SqlDbType.VarChar, 50));
           
            mySA.SelectCommand.Parameters["@customer_id"].Value = id;
            //mySA.SelectCommand.Parameters["@state"].Value = customer.state;
            //mySA.SelectCommand.Parameters["@zip"].Value = customer.zip;
            //mySA.SelectCommand.Parameters["@city"].Value = customer.city;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            residence = myDS.Tables[0].Rows[0]["address"].ToString() + " " +
                myDS.Tables[0].Rows[0]["state"].ToString() + " " + 
                myDS.Tables[0].Rows[0]["zip"].ToString() + " " +
                myDS.Tables[0].Rows[0]["city"].ToString();

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return residence;
    }

    //this procedure gets the names of all the customers
    public static List<Customer> GetCustomerNames(ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        Customer customer = null;
        List<Customer> customerList = new List<Customer>();

        try
        {
            string strSQL = "spGetCustomerNames";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            if (myDS.Tables[0].Rows.Count == 0)
                return null;

            for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
            {
                customer = new Customer();
                customer.customer_id = Convert.ToInt32(myDS.Tables[0].Rows[i]["customer_id"].ToString());
                customer.email = myDS.Tables[0].Rows[i]["email"].ToString();
                customer.first_name = myDS.Tables[0].Rows[i]["first_name"].ToString();
                customer.last_name = myDS.Tables[0].Rows[i]["last_name"].ToString();
                customer.address = myDS.Tables[0].Rows[i]["address"].ToString();
                customer.state = myDS.Tables[0].Rows[i]["state"].ToString();
                customer.zip = Convert.ToInt32(myDS.Tables[0].Rows[i]["zip"].ToString());
                customerList.Add(customer);
            }
        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }

        return customerList;
    }

    public static Customer GetCustomerDetails(int id, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        Customer customer = null;

        try
        {
            string strSQL = "spGetCustomerInfo";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.VarChar, 50));

            mySA.SelectCommand.Parameters["@customer_id"].Value = id;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            if (myDS.Tables[0].Rows.Count == 0)
                return null;

            customer = new Customer();
            customer.customer_id = Convert.ToInt32(myDS.Tables[0].Rows[0]["customer_id"].ToString());
            customer.email = myDS.Tables[0].Rows[0]["email"].ToString();
            customer.first_name = myDS.Tables[0].Rows[0]["first_name"].ToString();
            customer.last_name = myDS.Tables[0].Rows[0]["last_name"].ToString();
            customer.address = myDS.Tables[0].Rows[0]["address"].ToString();
            customer.state = myDS.Tables[0].Rows[0]["state"].ToString();
            customer.zip = Convert.ToInt32(myDS.Tables[0].Rows[0]["zip"].ToString());
            customer.city = (myDS.Tables[0].Rows[0]["city"].ToString());
            customer.webmaster = Convert.ToInt32(myDS.Tables[0].Rows[0]["webmaster"].ToString());
            customer.blockuser = Convert.ToInt32(myDS.Tables[0].Rows[0]["blockuser"].ToString());
        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }

        return customer;
    }

    /****************PRODUCT PROCS******************/

    //gets the display types of the product
    public static Dictionary<int, string> GetDisplay(ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        Dictionary<int, string> all_displays = new Dictionary<int,string>();
        try
        {
            string strSQL = "get_Display";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            if (myDS.Tables[0].Rows.Count == 0)
                return null;

            int id = 0;
            string type = "";
            for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
            {
                id = Convert.ToInt32(myDS.Tables[0].Rows[i]["display_id"].ToString());
                type = myDS.Tables[0].Rows[i]["display_type"].ToString();
                all_displays.Add(id, type);
            }

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return all_displays;
    }

    //gets the make of the product
    public static Dictionary<int, string> GetMake(ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        Dictionary<int, string> all_makes = new Dictionary<int, string>();
        try
        {
            string strSQL = "get_Make";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            int id = 0;
            string type = "";
            for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
            {
                id = Convert.ToInt32(myDS.Tables[0].Rows[i]["make_id"].ToString());
                type = myDS.Tables[0].Rows[i]["make"].ToString();
                all_makes.Add(id, type);
            }

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return all_makes;
    }

    //gets the refresh rates of the products
    public static Dictionary<int, int> GetRefresh(ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        Dictionary<int, int> all_refresh = new Dictionary<int, int>();
        try
        {
            string strSQL = "get_Refresh";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            int id = 0;
            int rate = 0;
            for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
            {
                id = Convert.ToInt32(myDS.Tables[0].Rows[i]["refresh_id"].ToString());
                rate = Convert.ToInt32(myDS.Tables[0].Rows[i]["refresh_rate"].ToString());
                all_refresh.Add(id, rate);
            }
        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return all_refresh;
    }

    //gets the resolutions of the products
    public static Dictionary<int, int> GetResolution(ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        Dictionary<int, int> all_resolutions = new Dictionary<int, int>();
        try
        {
            string strSQL = "get_Resolution";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            int id = 0;
            int res = 0;
            for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
            {
                id = Convert.ToInt32(myDS.Tables[0].Rows[i]["res_id"].ToString());
                res = Convert.ToInt32(myDS.Tables[0].Rows[i]["resolution"].ToString());
                all_resolutions.Add(id, res);
            }
        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return all_resolutions;
    }

    //gets the sizes of the products
    public static Dictionary<int, int> GetSize(ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        Dictionary<int, int> all_sizes = new Dictionary<int, int>();
        try
        {
            string strSQL = "get_Size";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            int id = 0;
            int siz = 0;
            for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
            {
                id = Convert.ToInt32(myDS.Tables[0].Rows[i]["size_id"].ToString());
                siz = Convert.ToInt32(myDS.Tables[0].Rows[i]["size"].ToString());
                all_sizes.Add(id, siz);
            }

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return all_sizes;
    }

    /**************ORDERS PROCS*******************/
    //Insert Orders
    public static void InsertOrders(Order order, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        try
        {
            string strSQL = "insert_Orders";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@customer_id", SqlDbType.Int));
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@date_created", SqlDbType.DateTime));


            //  mySA.SelectCommand.Parameters["@customer_id"].Value = customer.customer_id;
            mySA.SelectCommand.Parameters["@customer_id"].Value = order.customer_id;  
            mySA.SelectCommand.Parameters["@date_created"].Value = order.date_created;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
    }

    //insert_order_items
    public static void InsertOrderItems(OrderItem item, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        try
        {
            string strSQL = "insert_order_items";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@product_id", SqlDbType.Int));
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@quantity", SqlDbType.Int));


            //  mySA.SelectCommand.Parameters["@customer_id"].Value = customer.customer_id;
            mySA.SelectCommand.Parameters["@order_id"].Value = item.order_id;
            mySA.SelectCommand.Parameters["@product_id"].Value = item.product_id;
            mySA.SelectCommand.Parameters["@quantity"].Value = item.quantity;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
    }

    //get_order_products
    public static List<OrderItem> GetOrderProducts(int id, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        OrderItem item = null;
        List<OrderItem> itemList = new List<OrderItem>();
        try
        {
            string strSQL = "get_order_products";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));

            mySA.SelectCommand.Parameters["@order_id"].Value = id;


            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            if (myDS.Tables[0].Rows.Count == 0)
                return null;

            for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
            {
                item = new OrderItem();
                item.product_id = (int)myDS.Tables[0].Rows[0]["product_id"];
                item.quantity = (int)myDS.Tables[0].Rows[0]["quantity"];
                item.sub_total = Convert.ToDouble(myDS.Tables[0].Rows[0]["sub_total"]);
                itemList.Add(item);
            }

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }

        return itemList;
    }


    //get_order_totalprice
    public static double GetOrderTotalPrice(int id, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        Double price = 0;
        List<Order> itemList = new List<Order>();
        try
        {
            string strSQL = "get_order_totalprice";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));

            mySA.SelectCommand.Parameters["@order_id"].Value = id;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            price = Convert.ToDouble(myDS.Tables[0].Rows[0]["total_price"]);

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }

        return price;
    }

    //This procedure deletes all records
    public static void DeleteAllRecords(ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);

        try
        {
            string strSQL = "delete_all_records";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
    }

    //numOrderItemEntries
    public static int numOrderEntries(ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        int num = 0;
        try
        {
            string strSQL = "numOrderEntries";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            num = Convert.ToInt32(myDS.Tables[0].Rows[0]["numorders"]);

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }

        return num;
    }


    //numOrderItemEntries
    public static int numOrderItemEntries(ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        int num = 0;
        try
        {
            string strSQL = "numOrderItemEntries";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            num = Convert.ToInt32(myDS.Tables[0].Rows[0]["numitems"]);

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return num;
    }

    //this procedure places an order for the customer
    public static void PlaceOrder(int order_num, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        try
        {
            string strSQL = "place_Order";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));
            mySA.SelectCommand.Parameters["@order_id"].Value = order_num;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
    }

    //this procedure checks if a ordered is placed
    public static int SeeIfOrderPlaced(int order_num, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        int placed = -1;
        try
        {
            string strSQL = "isOrderPlaced";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@order_id", SqlDbType.Int));
            mySA.SelectCommand.Parameters["@order_id"].Value = order_num;
            
            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            placed = Convert.ToInt32(myDS.Tables[0].Rows[0]["is_ordered"].ToString());
        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return placed;
    }

    //this procedure returns the item searched for
    public static Product SearchItem(int display, int make, int size, int res, int refresh, int shipping, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        Product item = new Product();
        try
        {
            string strSQL = "search_item";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@display_type", SqlDbType.Int));
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@make", SqlDbType.Int));
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@size", SqlDbType.Int));
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@res", SqlDbType.Int));
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@refresh", SqlDbType.Int));
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@shipping", SqlDbType.Int));

            mySA.SelectCommand.Parameters["@display_type"].Value = display;
            mySA.SelectCommand.Parameters["@make"].Value = make;
            mySA.SelectCommand.Parameters["@size"].Value = size;
            mySA.SelectCommand.Parameters["@res"].Value = res;
            mySA.SelectCommand.Parameters["@refresh"].Value = refresh;
            mySA.SelectCommand.Parameters["@shipping"].Value = shipping;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            item.product_id = Convert.ToInt32(myDS.Tables[0].Rows[0]["product_id"].ToString());
            item.display_id = Convert.ToInt32(myDS.Tables[0].Rows[0]["display_id"].ToString());
            item.make_id = Convert.ToInt32(myDS.Tables[0].Rows[0]["make_id"].ToString());
            item.size_id = Convert.ToInt32(myDS.Tables[0].Rows[0]["size_id"].ToString());
            item.res_id = Convert.ToInt32(myDS.Tables[0].Rows[0]["res_id"].ToString());
            item.refresh_id = Convert.ToInt32(myDS.Tables[0].Rows[0]["refresh_id"].ToString());
            item.shipping = Convert.ToInt32(myDS.Tables[0].Rows[0]["shipping"].ToString());
            item.price = Convert.ToDouble(myDS.Tables[0].Rows[0]["price"].ToString());
            item.date_added = myDS.Tables[0].Rows[0]["date_added"].ToString();
            item.shipping = Convert.ToInt32(myDS.Tables[0].Rows[0]["shipping"].ToString());
            item.num_sales = Convert.ToInt32(myDS.Tables[0].Rows[0]["num_sales"].ToString());
            item.num_stocked = Convert.ToInt32(myDS.Tables[0].Rows[0]["num_stocked"].ToString());
   
        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }
        return item;
    }

    //this procedure returns the items in the customer's cart
    /*public static List<Product> SeeMyCart(int identity, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        List<Order> cart_items = new List<Product>();
        try
        {
            string strSQL = "see_my_cart";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@who_am_i", SqlDbType.Int));
            mySA.SelectCommand.Parameters["@who_am_i"].Value = identity;

            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            int id = 0;
            int siz = 0;
            for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
            {
                id = Convert.ToInt32(myDS.Tables[0].Rows[i]["size_id"].ToString());
                siz = Convert.ToInt32(myDS.Tables[0].Rows[i]["size"].ToString());
                all_sizes.Add(id, siz);
            }
        

        return cart_items;
    }*/

    //see_my_history
    public static List<OrderItem> SeeMyHistory(int id, ref List<string> errors)
    {
        SqlConnection conn = new SqlConnection(connection_string);
        OrderItem item = null;
        List<OrderItem> itemList = new List<OrderItem>();
        try
        {
            string strSQL = "see_my_history";

            SqlDataAdapter mySA = new SqlDataAdapter(strSQL, conn);
            mySA.SelectCommand.CommandType = CommandType.StoredProcedure;
            mySA.SelectCommand.Parameters.Add(new SqlParameter("@who_am_i", SqlDbType.Int));

            mySA.SelectCommand.Parameters["@who_am_i"].Value = id;


            DataSet myDS = new DataSet();
            mySA.Fill(myDS);

            if (myDS.Tables[0].Rows.Count == 0)
                return null;

            for (int i = 0; i < myDS.Tables[0].Rows.Count; i++)
            {
                item = new OrderItem();
                item.product_id = (int)myDS.Tables[0].Rows[0]["product_id"];
                item.quantity = (int)myDS.Tables[0].Rows[0]["quantity"];
                item.sub_total = Convert.ToDouble(myDS.Tables[0].Rows[0]["sub_total"]);
                itemList.Add(item);
            }

        }
        catch (Exception e)
        {
            errors.Add("Error: " + e.ToString());
        }
        finally
        {
            conn.Dispose();
            conn = null;
        }

        return itemList;
    }

    //public static void SearchItem()

    //public static void SeeTotalPrice()
    //public static void SeeMyHistory()
    //public static void UpdateCustomerResidence()


  
  }
}
